package com.scheduling.schedule.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.scheduling.schedule.entity.SchedulePlan;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * 排产计划Mapper接口
 *
 * @author 开发团队
 */
@Mapper
public interface SchedulePlanMapper extends BaseMapper<SchedulePlan> {

    /**
     * 根据状态查询排产计划
     *
     * @param status 计划状态
     * @return 排产计划列表
     */
    @Select("SELECT * FROM schedule_plan WHERE status = #{status} ORDER BY create_time DESC")
    List<SchedulePlan> selectByStatus(@Param("status") String status);

    /**
     * 根据算法类型查询排产计划
     *
     * @param algorithmType 算法类型
     * @return 排产计划列表
     */
    @Select("SELECT * FROM schedule_plan WHERE algorithm_type = #{algorithmType} ORDER BY create_time DESC")
    List<SchedulePlan> selectByAlgorithmType(@Param("algorithmType") String algorithmType);

    /**
     * 根据创建人查询排产计划
     *
     * @param createdBy 创建人
     * @return 排产计划列表
     */
    @Select("SELECT * FROM schedule_plan WHERE created_by = #{createdBy} ORDER BY create_time DESC")
    List<SchedulePlan> selectByCreatedBy(@Param("createdBy") String createdBy);

    /**
     * 查询最近的排产计划
     *
     * @param limit 查询数量
     * @return 排产计划列表
     */
    @Select("SELECT * FROM schedule_plan ORDER BY create_time DESC LIMIT #{limit}")
    List<SchedulePlan> selectRecentPlans(@Param("limit") int limit);

    /**
     * 根据排产计划ID更新关联订单状态
     *
     * @param planId 排产计划ID
     * @param status 新订单状态值
     * @return 更新的行数
     */
    @Update(
            "UPDATE `order` o " +
                    "JOIN ( " +
                    "    SELECT DISTINCT o.order_id " +
                    "    FROM schedule_plan sp " +
                    "    JOIN schedule_detail sd ON sp.plan_id = sd.plan_id " +
                    "    JOIN production_task pt ON sd.task_id = pt.task_id " +
                    "    JOIN work_order wo ON pt.work_order_id = wo.work_order_id " +
                    "    JOIN order_item oi ON wo.order_item_id = oi.order_item_id " +
                    "    JOIN `order` o ON oi.order_id = o.order_id " +
                    "    WHERE sp.plan_id = #{planId} " +
                    ") AS plan_orders " +
                    "ON o.order_id = plan_orders.order_id " +
                    "SET " +
                    "    o.status = #{status}, " +
                    "    o.update_time = NOW()"
    )
    int updateOrderStatusByPlanId(@Param("planId") String planId,
                                  @Param("status") String status);

    /**
     * 批量更新订单项状态
     *
     * @param planId 排产计划ID
     * @param status 新状态值
     * @return 更新的行数
     */
    @Update(
            "UPDATE order_item oi " +
                    "JOIN ( " +
                    "    SELECT DISTINCT oi.order_item_id " +
                    "    FROM schedule_plan sp " +
                    "    JOIN schedule_detail sd ON sp.plan_id = sd.plan_id " +
                    "    JOIN production_task pt ON sd.task_id = pt.task_id " +
                    "    JOIN work_order wo ON pt.work_order_id = wo.work_order_id " +
                    "    JOIN order_item oi ON wo.order_item_id = oi.order_item_id " +
                    "    WHERE sp.plan_id = #{planId} " +
                    ") AS plan_items " +
                    "ON oi.order_item_id = plan_items.order_item_id " +
                    "SET " +
                    "    oi.status = #{status}, " +
                    "    oi.create_time = oi.create_time" // 保持创建时间不变
    )
    int updateOrderItemStatusByPlanId(@Param("planId") String planId,
                                      @Param("status") String status);

    /**
     * 根据排产计划ID直接更新原料库存
     *
     * @param planId 排产计划ID
     * @return 更新影响的行数
     */
    @Update(
            "UPDATE material_inventory mi " +
                    "JOIN ( " +
                    "    SELECT om.material_id, SUM(pt.quantity * om.quantity) AS total_consumption " +
                    "    FROM schedule_plan sp " +
                    "    JOIN schedule_detail sd ON sp.plan_id = sd.plan_id " +
                    "    JOIN production_task pt ON sd.task_id = pt.task_id " +
                    "    JOIN operation op ON pt.operation_id = op.operation_id " +
                    "    JOIN operation_material om ON op.operation_id = om.operation_id " +
                    "    WHERE sp.plan_id = #{planId} " +
                    "    GROUP BY om.material_id " +
                    ") AS consumption " +
                    "ON mi.material_id = consumption.material_id " +
                    "SET " +
                    "    mi.quantity = GREATEST(0, mi.quantity - consumption.total_consumption), " +
                    "    mi.last_update = NOW()"
    )
    int updateInventoryByPlanId(@Param("planId") String planId);

    /**
     * 根据排产计划ID更新关联工单状态
     *
     * @param planId    排产计划ID
     * @param newStatus 新状态值
     * @return 更新影响的行数
     */
    @Update(
            "UPDATE work_order wo " +
                    "JOIN ( " +
                    "    SELECT DISTINCT pt.work_order_id " +
                    "    FROM schedule_plan sp " +
                    "    JOIN schedule_detail sd ON sp.plan_id = sd.plan_id " +
                    "    JOIN production_task pt ON sd.task_id = pt.task_id " +
                    "    WHERE sp.plan_id = #{planId} " +
                    ") AS plan_orders " +
                    "ON wo.work_order_id = plan_orders.work_order_id " +
                    "SET " +
                    "    wo.status = #{newStatus}, " +
                    "    wo.update_time = NOW()"
    )
    int updateWorkOrdersByPlanId(@Param("planId") String planId,
                                 @Param("newStatus") String newStatus);

    /**
     * 根据排产计划ID更新关联机器状态
     *
     * @param planId    排产计划ID
     * @param newStatus 新状态值
     * @return 更新影响的行数
     */
    @Update(
            "UPDATE machine " +
                    "    SET " +
                    "        status = #{newStatus}, " +
                    "        update_time = NOW() " +
                    "    WHERE machine_id IN ( " +
                    "        SELECT DISTINCT pt.machine_id " +
                    "        FROM schedule_plan sp " +
                    "        JOIN schedule_detail sd ON sp.plan_id = sd.plan_id " +
                    "        JOIN production_task pt ON sd.task_id = pt.task_id " +
                    "        WHERE sp.plan_id = #{planId} " +
                    "          AND pt.machine_id IS NOT NULL " +
                    "    )"
    )
    int updateMachineStatusByPlanId(@Param("planId") String planId,
                                    @Param("newStatus") String newStatus);
} 